﻿-- 查询在场人员
CREATE procedure [dbo].[GetCurrentInData]
@IsClearMode bit -- 是否清空模式
as
if(@IsClearMode =0)
begin
delete from Kqjl_Info_Copy where kqsj<CONVERT(varchar(10), GETDATE(), 120)
end
select isnull(qybmm,'当前总在场人数') qybmm,sum(cnt) cnt
from
(
select isnull(case qybmm when '' then null else qybmm end,'其他') qybmm,count(1) cnt from Kqjl_Info_Copy
inner join
(select max(kqsj) sj,sfzh from Kqjl_Info_Copy group by sfzh) aa on(Kqjl_Info_Copy.kqsj=aa.sj and Kqjl_Info_Copy.sfzh=aa.sfzh)
where jczt=1 and sfff=0 
group by qybmm
) aa
group by qybmm
with rollup

select sum(case jczt when 1 then 1 else -1 end) aa
from Kqjl_info_Copy
where sfff=1